using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using TienTeTuong;
using log4net;
using TienTeTuong.DTO;

namespace TienTeTuong
{
	/// <summary>
	/// Data access layer class for Article
	/// </summary>
	class ArticleDAO 
	{
        /// <summary>
        /// Get list articles by viewed, using in default page
        /// if top = 0 get all orderby viewed else get follow top
        /// </summary>
        /// <param name="top"></param>
        /// <returns></returns>
        public List<ArticleDTO> GetArticleByViewed(int top)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstArticle = new List<ArticleDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                    string strSQL = @"SELECT * FROM article ORDER BY Viewed DESC";
                    if(top != 0)
                    {
                        strSQL += " LIMIT " + top;
                    }
                    MysqlCmd.CommandText = strSQL;
                    reader = MysqlCmd.ExecuteReader();
                    while (reader.Read())
                    {
                        try
                        {
                            var cate = new ArticleDTO();
                            cate.ArtID = reader[0].ToString();
                            cate.ArtCateID = reader[1].ToString();
                            cate.Title = reader[2].ToString();
                            cate.ShortDescript = reader[3].ToString();
                            cate.Content = reader[4].ToString();
                            cate.Active = reader[5].ToString() == "1";
                            cate.Status = int.Parse(reader[6].ToString());
                            cate.VipLevel = int.Parse(reader[7].ToString());
                            cate.Viewed = double.Parse(reader[8].ToString());
                            cate.Image = reader[9].ToString();
                            cate.Author = reader[10].ToString();
                            cate.Hot = reader[11].ToString() == "1";
                            cate.CreatedDate = Convert.ToDateTime(reader[12].ToString());
                            cate.UpdatedDate = Convert.ToDateTime(reader[13].ToString());
                            lstArticle.Add(cate);

                        }
                        catch (Exception ex)
                        {
                            Logger.WriteLog(LogLevel.ERROR,ex +"\n" +ex.StackTrace );
                        }


                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstArticle;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstArticle;
            }
        }

        /// <summary>
        /// Get list articles by created date, using in default page
        /// if top = 0 get all orderby created date else get follow top
        /// </summary>
        /// <param name="top"></param>
        /// <returns></returns>
        public List<ArticleDTO> GetNewArticles(int top, string exceptArticle)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstArticle = new List<ArticleDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                string strSQL = @"SELECT * FROM `article` WHERE `ArtID` <> '" + exceptArticle + "'  and `Active` = '1' ORDER BY `CreatedDate`DESC";
                if (top != 0)
                {
                    strSQL += " LIMIT " + top;
                }
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cate = new ArticleDTO();
                        cate.ArtID = reader[0].ToString();
                        cate.ArtCateID = reader[1].ToString();
                        cate.Title = reader[2].ToString();
                        cate.ShortDescript = reader[3].ToString();
                        cate.Content = reader[4].ToString();
                        cate.Active = reader[5].ToString() == "1";
                        cate.Status = int.Parse(reader[6].ToString());
                        cate.VipLevel = int.Parse(reader[7].ToString());
                        cate.Viewed = double.Parse(reader[8].ToString());
                        cate.Image = reader[9].ToString();
                        cate.Hot = reader[11].ToString() == "1";
                        cate.CreatedDate = Convert.ToDateTime(reader[12].ToString());
                        cate.UpdatedDate = Convert.ToDateTime(reader[13].ToString());
                        lstArticle.Add(cate);

                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }

                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstArticle;

            }
            catch
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstArticle;
            }
        }
        /// <summary>
        /// Get list article by category article ID
        /// </summary>
        /// <param name="CateArtID"></param>
        /// <returns></returns>
        public List<ArticleDTO> GetArticleByCateArticleID(string CateArtID)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstArticle = new List<ArticleDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                string strSQL = @"SELECT * FROM article Where ArtCateID='" + CateArtID + "'  and `Active` = '1'";
             
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cate = new ArticleDTO();
                        cate.ArtID = reader[0].ToString();
                        cate.ArtCateID = reader[1].ToString();
                        cate.Title = reader[2].ToString();
                        cate.ShortDescript = reader[3].ToString();
                        cate.Content = reader[4].ToString();
                        cate.Active = reader[5].ToString() == "1";
                        cate.Status = int.Parse(reader[6].ToString());
                        cate.VipLevel = int.Parse(reader[7].ToString());
                        cate.Viewed = double.Parse(reader[8].ToString());
                        cate.Image = reader[9].ToString();
                        cate.Author = reader[10].ToString();
                        cate.Hot = reader[11].ToString() == "1";
                        cate.CreatedDate = Convert.ToDateTime(reader[12].ToString());
                        cate.UpdatedDate = Convert.ToDateTime(reader[13].ToString());
                        lstArticle.Add(cate);

                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }


                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstArticle;

            }
            catch (Exception ex)
            {
                Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstArticle;
            }
        }

        /// <summary>
        /// Get  article detail by article ID
        /// </summary>
        /// <param name="CateArtID"></param>
        /// <returns></returns>
        public ArticleDTO GetArticleDetailByID(string ArticaleID)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var cate = new ArticleDTO();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                string strSQL = @"SELECT * FROM article WHERE ArtID='" + ArticaleID+ "'";

                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        //var cate = new ArticleDTO();
                        cate.ArtID = reader[0].ToString();
                        cate.ArtCateID = reader[1].ToString();
                        cate.Title = reader[2].ToString();
                        cate.ShortDescript = reader[3].ToString();
                        cate.Content = reader[4].ToString();
                        cate.Active = reader[5].ToString() == "1";
                        cate.Status = int.Parse(reader[6].ToString());
                        cate.VipLevel = int.Parse(reader[7].ToString());
                        cate.Viewed = double.Parse(reader[8].ToString());
                        cate.Image = reader[9].ToString();
                        cate.Author = reader[10].ToString();
                        cate.Hot = reader[11].ToString() == "1";
                        cate.CreatedDate = Convert.ToDateTime(reader[12].ToString());
                        cate.UpdatedDate = Convert.ToDateTime(reader[13].ToString());
                    //   lstArticle.Add(cate);

                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }

                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return cate;

            }
            catch (Exception ex)
            {
                Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return cate;
            }
        }

        public List<ArticleDTO> GetListArticleDetailByID(string cateArtID)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstListArticle = new List<ArticleDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                string strSQL = @"SELECT * FROM article WHERE ArtCateID='" + cateArtID + "'";

                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cate = new ArticleDTO();
                        cate.ArtID = reader[0].ToString();
                        cate.ArtCateID = reader[1].ToString();
                        cate.Title = reader[2].ToString();
                        cate.ShortDescript = reader[3].ToString();
                        cate.Content = reader[4].ToString();
                        cate.Active = reader[5].ToString() == "1";
                        cate.Status = int.Parse(reader[6].ToString());
                        cate.VipLevel = int.Parse(reader[7].ToString());
                        cate.Viewed = double.Parse(reader[8].ToString());
                        cate.Image = reader[9].ToString();
                        cate.Author = reader[10].ToString();
                        cate.Hot = reader[11].ToString() == "1";
                        cate.CreatedDate = Convert.ToDateTime(reader[12].ToString());
                        cate.UpdatedDate = Convert.ToDateTime(reader[13].ToString());
                        lstListArticle.Add(cate);

                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }

                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstListArticle;

            }
            catch (Exception ex)
            {
                Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstListArticle;
            }
        }
        /// <summary>
        /// Get list article by category article ID
        /// </summary>
        /// <param name="CateArtID"></param>
        /// <returns></returns>
        public List<ArticleDTO> GetArticleByPage(string cateArtID, int offsetFirstRow, int numPerpage)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstArticle = new List<ArticleDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                string strSQL = @"SELECT * FROM article ";
                if (!string.IsNullOrEmpty(cateArtID))
                {
                    strSQL += " Where ArtCateID='" + cateArtID + "' and `Active` = '1'";
                }
                else 
                {
                    strSQL += " Where `Active` = '1'";
                }
                strSQL += " ORDER BY CreatedDate DESC LIMIT " + offsetFirstRow + "," + numPerpage;
                

                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cate = new ArticleDTO();
                        cate.ArtID = reader[0].ToString();
                        cate.ArtCateID = reader[1].ToString();
                        cate.Title = reader[2].ToString();
                        cate.ShortDescript = reader[3].ToString();
                        cate.Content = reader[4].ToString();
                        cate.Active = reader[5].ToString() == "1";
                        cate.Status = int.Parse(reader[6].ToString());
                        cate.VipLevel = int.Parse(reader[7].ToString());
                        cate.Viewed = double.Parse(reader[8].ToString());
                        cate.Image = reader[9].ToString();
                        cate.Author = reader[10].ToString();
                        cate.Hot = reader[11].ToString() == "1";
                        cate.CreatedDate = Convert.ToDateTime(reader[12].ToString());
                        cate.UpdatedDate = Convert.ToDateTime(reader[13].ToString());
                        lstArticle.Add(cate);

                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }

                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstArticle;

            }
            catch (Exception ex)
            {
                Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstArticle;
            }
        }
        /// <summary>
        /// Get list article by category article ID
        /// </summary>
        /// <param name="CateArtID"></param>
        /// <returns></returns>
        public List<ArticleDTO> SearchArticleByPage(string keyword, int offsetFirstRow, int numPerpage)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstArticle = new List<ArticleDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                string strSQL = @"SELECT * FROM article WHERE Title LIKE N'%" 
                               + keyword +"%' OR ShortDescript LIKE N'%"
                               + keyword +"%' OR Content LIKE N'%" 
                               +keyword +"%'";
                
                strSQL += " ORDER BY CreatedDate DESC LIMIT " + offsetFirstRow + "," + numPerpage;


                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cate = new ArticleDTO();
                        cate.ArtID = reader[0].ToString();
                        cate.ArtCateID = reader[1].ToString();
                        cate.Title = reader[2].ToString();
                        cate.ShortDescript = reader[3].ToString();
                        cate.Content = reader[4].ToString();
                        cate.Active = reader[5].ToString() == "1";
                        cate.Status = int.Parse(reader[6].ToString());
                        cate.VipLevel = int.Parse(reader[7].ToString());
                        cate.Viewed = double.Parse(reader[8].ToString());
                        cate.Image = reader[9].ToString();
                        cate.Author = reader[10].ToString();
                        cate.Hot = reader[11].ToString() == "1";
                        cate.CreatedDate = Convert.ToDateTime(reader[12].ToString());
                        cate.UpdatedDate = Convert.ToDateTime(reader[13].ToString());
                        lstArticle.Add(cate);

                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }

                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstArticle;

            }
            catch (Exception ex)
            {
                Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstArticle;
            }
        }

        public int GetSearchNumArticles(string keyword)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            int toTal = 0;
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                string strSQL = @"SELECT Count(*) FROM article WHERE Title LIKE N'%"
                                + keyword + "%' OR ShortDescript LIKE N'%"
                                + keyword + "%' OR Content LIKE N'%"
                                + keyword + "%'";
                MysqlCmd.CommandText = strSQL;

                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    toTal = int.Parse(reader[0].ToString());
                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                // return lstCategory;
                return toTal;
            }
            catch (Exception ex)
            {
                Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                if (reader != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return toTal;
            }
        }
        public int GetNumArticles(string ArtCateID)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            int toTal = 0;
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                string strSQL = @"SELECT Count(*) FROM article ";
                if(!string.IsNullOrEmpty(ArtCateID))
                {
                    strSQL += " WHERE ArtCateID='" + ArtCateID + "'";
                }
                MysqlCmd.CommandText = strSQL;

                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    toTal = int.Parse(reader[0].ToString());
                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                // return lstCategory;
                return toTal;
            }
            catch (Exception ex)
            {
                Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                if (reader != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return toTal;
            }
        }

        public List<ArticleDTO> GetArticleDetailByTime(string month, string year)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstArticle = new List<ArticleDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                string strSQL = @"SELECT * FROM `article`  WHERE `LunarDate` >= '" + year + "/" + month +
                                "/1 00:00:00' AND `LunarDate` <= '" + year + "/" + month + "/" +
                                DateTime.DaysInMonth(int.Parse(year), int.Parse(month)) + " 23:59:59'";

                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cate = new ArticleDTO();
                        cate.ArtID = reader[0].ToString();
                        cate.ArtCateID = reader[1].ToString();
                        cate.Title = reader[2].ToString();
                        cate.ShortDescript = reader[3].ToString();
                        cate.Content = reader[4].ToString();
                        cate.Active = reader[5].ToString() == "1";
                        cate.Status = int.Parse(reader[6].ToString());
                        cate.VipLevel = int.Parse(reader[7].ToString());
                        cate.Viewed = double.Parse(reader[8].ToString());
                        cate.Image = reader[9].ToString();
                        cate.Author = reader[10].ToString();
                        cate.Hot = reader[11].ToString() == "1";
                        cate.CreatedDate = Convert.ToDateTime(reader[12].ToString());
                        cate.UpdatedDate = Convert.ToDateTime(reader[13].ToString());
                        lstArticle.Add(cate);

                    }
                    catch (Exception)
                    {

                    }

                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstArticle;

            }
            catch (Exception ex)
            {
                Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstArticle;
            }
        }

        public List<CommentDTO> GetCommentByArticleID(string artID, int top)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstComment = new List<CommentDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                string strSQL = @"SELECT * FROM `comment`  WHERE `IsShown` = 1 AND `ArtID` = '" + artID +
                                "' ORDER BY  `comment`.`CreatedDate` DESC ";
                if (top != 0)
                {
                    strSQL += " LIMIT " + top;
                }
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cmt = new CommentDTO();
                        cmt.CmtID = reader[0].ToString();
                        cmt.ArtID = reader[1].ToString();
                        cmt.PName = reader[2].ToString();
                        cmt.PEmail = reader[3].ToString();
                        cmt.Content = reader[4].ToString();
                        cmt.IsShown = reader[5].ToString() == "1";
                        cmt.CreatedDate = Convert.ToDateTime(reader[6].ToString());
                        lstComment.Add(cmt);

                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }

                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstComment;

            }
            catch (Exception ex)
            {
                Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstComment;
            }
        }
        
        public bool InsertNewCommentByArticleID(string artID, CommentDTO cmt)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                string strSQL =
                    @"INSERT INTO `comment` (`CmtID`, `ArtID`, `PName`, `PEmail`, `Content`, `IsShown`, `CreatedDate`) VALUES ('" +
                    Guid.NewGuid().ToString() + "', '" + artID + "', '" + cmt.PName + "', '" + cmt.PEmail + "' , '" +
                    cmt.Content + "',  '1', '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "');";
                
                MysqlCmd.CommandText = strSQL;
                int val = MysqlCmd.ExecuteNonQuery();
                
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return val > 0;

            }
            catch (Exception ex)
            {
                Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return false;
            }
        }

        public bool UpdateViewedNumberByArticleID(string artID, double viewedNumber)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                string strSQL = @"UPDATE ARTICLE SET Viewed= "+viewedNumber + " WHERE ArtID='" +artID+"'";

                MysqlCmd.CommandText = strSQL;
                int val = MysqlCmd.ExecuteNonQuery();

                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return val > 0;

            }
            catch (Exception ex)
            {
                Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return false;
            }
        }

        public List<ArticleDTO> GetAllArticle()
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstArticle = new List<ArticleDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                string strSQL = @"SELECT * FROM article ORDER BY createdDate DESC";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cate = new ArticleDTO();
                        cate.ArtID = reader[0].ToString();
                        cate.ArtCateID = reader[1].ToString();
                        cate.Title = reader[2].ToString();
                        cate.ShortDescript = reader[3].ToString();
                        cate.Content = reader[4].ToString();
                        cate.Active = reader[5].ToString() == "1";
                        cate.Status = int.Parse(reader[6].ToString());
                        cate.VipLevel = int.Parse(reader[7].ToString());
                        cate.Viewed = double.Parse(reader[8].ToString());
                        cate.Image = reader[9].ToString();
                        cate.Author = reader[10].ToString();
                        cate.Hot = reader[11].ToString() == "1";
                        cate.CreatedDate = Convert.ToDateTime(reader[12].ToString());
                        cate.UpdatedDate = Convert.ToDateTime(reader[13].ToString());
                        lstArticle.Add(cate);

                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }


                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstArticle;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstArticle;
            }
        }

        public bool delete(string key)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            int reader = 0;
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                //  var strSQL = @"SELECT a.OptionID,a.ProID,b.Name,b.Price,a.Quantity FROM CateDetailProduct AS a JOIN Productdetail AS b ON a.ProID = b.ProID Where OptionID='" + optionID + "'";
                //var strSQL = @"UPDATE `tientetuong`.`category` SET `Active` = b '0' WHERE `category`.`CateID` = '" + key + "'";
                var strSQL = @"Delete FROM `tientetuong`.`article` where `article`.`ArtID` = '" + key + "'";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteNonQuery();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return reader > 0;
            }
            catch (Exception ex)
            {
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return false;
            }
        }

        public bool update(ArticleDTO item)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            int reader = 0;
            try
            {
                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                //  var strSQL = @"SELECT a.OptionID,a.ProID,b.Name,b.Price,a.Quantity FROM CateDetailProduct AS a JOIN Productdetail AS b ON a.ProID = b.ProID Where OptionID='" + optionID + "'";
                var strSQL = @"UPDATE `article` SET  `ArtCateID` =  '" + item.ArtCateID + "', "
                + "`Title` =  '" + item.Title + "', "
                    + "`ShortDescript` =  '" + item.ShortDescript + "', "
                    + "`Content` =  '" + item.Content + "', "
               + "`Image` =  '" + item.Image + "', "
               + "`Status` =  '" + item.Status.ToString() + "',"
               + "`VipLevel` =  '" + item.VipLevel.ToString() + "',"
               + "`Image` =  '" + item.Image + "',"
               + "`Author` =  '" + item.Author + "',"
               + "`Active` =  b'" + (bool.Parse(item.Active.ToString()) ? "1" : "0") + "',"
               + "`Hot` =  b'" + (bool.Parse(item.Hot.ToString()) ? "1" : "0") + "'"
               + " WHERE  `article`.`ArtID` =  '" + item.ArtID + "';";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteNonQuery();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return reader > 0;
            }
            catch (Exception ex)
            {
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return false;
            }
        }

        public bool add(ArticleDTO item)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            int reader = 0;
            try
            {
                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                //  var strSQL = @"SELECT a.OptionID,a.ProID,b.Name,b.Price,a.Quantity FROM CateDetailProduct AS a JOIN Productdetail AS b ON a.ProID = b.ProID Where OptionID='" + optionID + "'";
                var strSQL = @"Insert into `article` (`ArtID`, `ArtCateID`, `Title`, `ShortDescript`, `Content`, `Active`, `Status`, `VipLevel`, `Image`, `Author`, `Hot`)"
                    + " Values ('" + item.ArtID + "', '" + item.ArtCateID + "', '" + item.Title + "', '" + item.ShortDescript + "', '" + item.Content + "', b'" + ((bool)item.Active ? "1" : "0") + "', '" + item.Status.ToString() + "', '" + item.VipLevel.ToString() + "', '" + item.Image
                    + "', '" + item.Author + "', b'" + ((bool)item.Hot ? "1" : "0") + "');";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteNonQuery();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return reader > 0;
            }
            catch (Exception ex)
            {
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return false;
            }
        }
    }
}
